<?php

namespace App\Admin\Controllers;

use App\Admin\Extensions\ExcelExpoter;
use App\Admin\Extensions\Squad_monthExpoter;
use App\Models\Class_section_group;
use App\Models\Class_type;
use App\Models\Credits_squad;
use App\Models\Squad;
use App\Models\Squad_month;
use App\Models\Student;
use App\Models\Teacher;

use Encore\Admin\Auth\Database\Administrator;
use Encore\Admin\Form;
use Encore\Admin\Grid;
use Encore\Admin\Facades\Admin;
use Encore\Admin\Layout\Content;
use App\Http\Controllers\Controller;
use Encore\Admin\Controllers\ModelForm;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\Credits;
use Encore\Admin\Widgets\Table;
use App\Models\Campus;
use Maatwebsite\Excel\Facades\Excel;

class Squad_monthController extends Controller
{
    /**
     * Index interface.
     *
     * @return Content
     */
    public function index()
    {
        return Admin::content(function (Content $content) {
            $content->header('班级月度数据汇总');
            $content->description(trans('admin.list'));
            
            $content->body($this->grid()->render());
            //$content->body(view('admin.credits_rules'));
        });
    }

    /**
     * Edit interface.
     *
     * @param $id
     * @return Content
     */
    public function edit($id)
    {
        return Admin::content(function (Content $content) use ($id) {

            $content->header(trans('admin.teacher'));
            $content->description(trans('admin.edit'));

            $content->body($this->form()->edit($id));
        });
    }

    /**
     * Create interface.
     *
     * @return Content
     */
    public function create()
    {
        return Admin::content(function (Content $content) {

            $content->header(trans('admin.teacher'));
            $content->description(trans('admin.create'));

            $content->body($this->form());
        });
    }

    /**
     * Make a grid builder.
     *
     * @return Grid
     */
    protected function grid()
    {
        return Admin::grid(Squad_month::class, function (Grid $grid) {
            $user = Admin::user()->id;

            $section = DB::table('class_section_groups')->whereRaw('find_in_set('.$user.',`charge`)')->count();
            if($section > 0){
                //是班段负责人，返回该班段下所有班级
                $section_id = DB::table('class_section_groups')->where('pid','=',0)->whereRaw('find_in_set('.$user.',`charge`)')->get(['id']);
                $arr = array();
                foreach($section_id as $v){
                    $arr[] = $v->id;
                }
                $data = DB::table('squads')->whereIn('section',$arr)->get(['id']);
            }else{
                $group = DB::table('class_section_groups')->where('pid','!=',0)->whereRaw('find_in_set('.$user.',`charge`)')->count();
                if($group>0){
                    //是该分组负责人，直接返回该分组下所有班级
                    $section_id = DB::table('class_section_groups')->where('pid','!=',0)->whereRaw('find_in_set('.$user.',`charge`)')->get(['id']);
                    $arr = array();
                    foreach($section_id as $v){
                        $arr[] = $v->id;
                    }
                    $data = DB::table('squads')->whereIn('group',$arr)->get(['id']);
                }else{
                    //不是该分组负责人，返回带的班级
                    $data = DB::table('squads')->where('class_teacher','=',$user)->get(['id']);
                }
            }

            $arr = array();
            foreach($data as $v){
                if(isset($v->id) && !is_null($v->id)){
                    $arr[] = $v->id;
                }else{
                    $arr[] = 0;
                }
            }
            $grid->model()->whereIn('squad_id',$arr);
            $grid->column('时间')->display(function(){
                return $this->year.'年'.$this->season.'学期'.$this->week.'周';
            });
            $grid->squad()->section('班段')->display(function($section){
                return Class_section_group::find($section)->name;
            });
            $grid->squad()->group('分组')->display(function($group){
                return Class_section_group::find($group)->name;
            });
            $grid->squad()->type('班型')->display(function($type){
                return Class_type::find($type)->name;
            });
            $grid->squad()->no('班号');
            $grid->squad()->class_teacher('班主任')->display(function($class_teacher){
                $temp = DB::table('admin_users')->where('id',$class_teacher)->get(['en_name']);
                foreach($temp as $v){
                    return $v->en_name;
                }
            });
            $grid->column('班级人数')->display(function(){
                return DB::table('students')->where('squad_id',$this->squad_id)->count();
            });
            $grid->column('查看')->display(function(){
                return '<a class="fa fa-eye" href="/admin/squad_month/view/'.$this->id.'"></a>';
            });

            //禁用导出按钮
            $grid->disableExport();
            $grid->disableRowSelector();
            $grid->disableCreateButton();
            //$grid->disableFilter();

            $grid->disableActions();
            $grid->filter(function ($filter) {
                $filter->column(1/2, function ($filter) {
                    $filter->disableIdFilter();
                    $filter->equal('year','年份')->default(date('Y'));
                    $filter->equal('season','学期')->select(function(){
                        return array('1'=>'春季学期','2'=>'秋季学期');
                    });
                    $filter->where(function($query){
                        $query->whereHas('Squad', function ($query) {
                            $query->where('section','=',$this->input);
                        });
                    },'班段')->select(Class_section_group::where('pid','=','0')->get()->pluck('name', 'id'));
                    $filter->where(function($query){
                        $query->whereHas('Squad', function ($query) {
                            $query->where('type','=',$this->input);
                        });
                    },'班型')->select(Class_type::get()->pluck('name', 'id'));
                });
                $filter->column(1/2, function ($filter) {
                    $filter->disableIdFilter();
                    $filter->equal('week','周')->select(function(){
                        $arr = array();
                        for($i=1;$i<25;$i++){
                            $arr[$i] = '第'.$i.'周';
                        }
                        return $arr;
                    });
                    $filter->where(function($query){
                        $query->whereHas('Squad', function ($query) {
                            $query->where('group','=',$this->input);
                        });
                    },'分组')->select(Class_section_group::where('pid','>','0')->get()->pluck('name', 'id'));
                    $filter->where(function($query){
                        $query->whereHas('Squad', function ($query) {
                            $query->where('no','=',$this->input);
                        });
                    },'班号');
                });
            });
        });
    }

    /**
     * Make a form builder.
     *
     * @return Form
     */
    protected function form()
    {
        return Admin::form(Teacher::class, function (Form $form) {
            $form->display('id', 'ID');

            $form->text('ch_name', trans('admin.ch_name'))->rules('required')->addElementLabelClass('must-input');
            $form->text('en_name', trans('admin.en_name'))->rules('required')->addElementLabelClass('must-input');
            $form->select('sex', trans('admin.sex'))->options([1 => trans('admin.men'), 2 => trans('admin.women')]);
            $form->email('email', trans('admin.email'));
            $form->mobile('tel', trans('admin.tel'))->options(['mask' => '999 9999 9999']);
            //去掉添加/编辑表单的重置按钮
            $form->disableReset();
        });
    }

    public function rule_save(Request $request)
    {
        $num = DB::table('credits_rules')->count();

        $data = array();
        foreach($request->request as $key => $v){
            $data[$key] = $v;
        }
        if($num > 0){
            DB::table('credits_rules')->update($data);
        }else{
            DB::table('credits_rules')->insert($data);
        }
        return array('message'=>'操作成功！');
    }

    public function squad_month_view($id)
    {
        return Admin::content(function (Content $content) use ($id) {
            $content->header('班级周报');
            $content->description(trans('admin.list'));

            $content->body($this->view_grid($id)->render());
            $content->body($this->credits_rule_view($id));
        });
    }

    public function credits_rule_view($id)
    {
        $data = $this->get_every_data($id);
        return view('admin.squad_month',compact('data'));
    }

    protected function view_grid($id)
    {
        $grid = new Grid(new Squad_month());
        $grid->model()->where('id',$id);
/*        $grid->student()->no('学号');
        $grid->student()->ch_name('中文名');
        $grid->student()->en_name('英文名');*/


        $squad_month = DB::table('squad_month')->where('id',$id)->get();
        foreach($squad_month as $v){
            $squad = Squad::where('id',$v->squad_id)->get()->toArray();
        }

        $grid->column('时间')->display(function(){
            return $this->year.'年'.$this->season.'学期'.$this->week.'周';
        });
/*        $grid->squad()->section('班段')->display(function($section){
            return Class_section_group::find($section)->name;
        });*/
        $grid->column('班段')->display(function() use ($squad){
            return Class_section_group::find($squad[0]['section'])->name;
        });
        $grid->column('开班批次')->display(function() use ($squad){
            return date('Y',strtotime($squad[0]['batch'])).'年'.date('m',strtotime($squad[0]['batch'])).'月';
        });
        $grid->column('分组')->display(function() use ($squad){
            return Class_section_group::find($squad[0]['group'])->name;
        });
        $grid->column('班型')->display(function() use ($squad){
            return Class_type::find($squad[0]['type'])->name;
        });
        $grid->column('班号')->display(function() use ($squad){
            return $squad[0]['no'];
        });
        $grid->column('班主任')->display(function() use ($squad){
            return Administrator::find($squad[0]['class_teacher'])->en_name;
        });

        $grid->column('课程详情')->expand(function(){
            $arr = json_decode($this->content,true);

            return new Table([],$arr);
        }, '展开');

        $grid->kq_rate('全勤率')->display(function($kq_rate){
            return round($kq_rate*100,2).'%';
        });

        $grid->zy_rate('作业准交率')->display(function($zy_rate){
            return round($zy_rate*100,2).'%';
        });


        $grid->jx_rate('家校准交率')->display(function($jx_rate){
            return round($jx_rate*100,2).'%';
        });

        $grid->test_rate('测试准交率')->display(function($test_rate){
            return round($test_rate*100,2).'%';
        });

        $grid->test_avg('测试平均分')->display(function($test_avg){
            return $test_avg;
        });

        $grid->wastage_num('流失人数')->display(function($wastage_num){
            return $wastage_num;
        });
        $grid->wastage_rate('流失率')->display(function($wastage_rate){
            return round($wastage_rate*100,2).'%';
        });


        $grid->exporter(new Squad_monthExpoter($grid,'班级学分记录',$id));
        //$grid->disableExport();
        //$grid->disableRowSelector();
        $grid->disableCreateButton();
        $grid->tools(function ($tools) {
            //关闭批量删除
            $tools->batch(function ($batch) {
                $batch->disableDelete();
            });
        });

        $grid->disableActions();
        return $grid;
    }

    public function credits_rule_view_edit()
    {
        return Admin::content(function (Content $content){
            $content->header('学分表');
            $content->description('配置');

            $content->body($this->credits_rule_view_grid());
            //$content->body($this->view_grid($id)->render());
        });
    }

    public function credits_rule_view_grid()
    {
        $rule = DB::table('credits_rules')->get();
        $rule = json_encode($rule);
        return view('admin.credits_rules_edit',compact('rule'));
    }

    public function get_every_data($id)
    {
        $squad = Squad_month::find($id);
        $squad_month = Squad_month::where('year',(int)$squad->year)->where('season',(int)$squad->season)->where('week',(int)$squad->week)->get()->toArray();
        $squad_month_temp = array();
        foreach($squad_month as $v){
            $squad_month_temp[$v['squad_id']] = $v;
        }

        $squad_table = Squad::find($squad->squad_id);

        //获取班段情况
        $section = Squad::where('section',$squad_table->section)->get()->toArray();
        $kq_total=$zy_total=$jx_total=$lost_total=$lost_num=0;
        $kq_count = $zy_count = $jx_count = $lost_count = 0;
        foreach($section as $v){
            //计算平均时要排除0
            if(isset($squad_month_temp[$v['id']]) && !empty($squad_month_temp[$v['id']])){
                $kq_total += $squad_month_temp[$v['id']]['kq_rate'];
                if($squad_month_temp[$v['id']]['kq_rate'] > 0){
                    $kq_count ++;
                }

                $zy_total += $squad_month_temp[$v['id']]['zy_rate'];
                if($squad_month_temp[$v['id']]['zy_rate'] > 0){
                    $zy_count ++;
                }

                $jx_total += $squad_month_temp[$v['id']]['jx_rate'];
                if($squad_month_temp[$v['id']]['jx_rate'] > 0){
                    $jx_count ++;
                }

                $lost_total += $squad_month_temp[$v['id']]['wastage_rate'];
                $lost_num += $squad_month_temp[$v['id']]['wastage_num'];
            }
        }
        $lost_count = count($section);
        if($lost_count == 0){
            $lost_count = 1;
        }
        if($kq_count == 0){
            $kq_count = 1;
        }
        if($jx_count == 0){
            $jx_count = 1;
        }
        if($zy_count == 0){
            $zy_count = 1;
        }

        $temp = $data = array();
        $temp = array('班段',Class_section_group::find($squad_table->section)->name,'-','-','-',round($kq_total*100/$kq_count,2).'%',round($jx_total*100/$jx_count,2).'%',round($zy_total*100/$zy_count,2).'%',round($lost_total*100/$lost_count,2).'%',$lost_num);
        $data[] = $temp;

        //获取分组情况
        $group = Squad::where('group',$squad_table->group)->get()->toArray();
        $kq_total=$zy_total=$jx_total=$lost_total=$lost_num=0;
        $kq_count = $zy_count = $jx_count = $lost_count = 0;
        foreach($group as $v){
            //计算平均时要排除0
            if(isset($squad_month_temp[$v['id']]) && !empty($squad_month_temp[$v['id']])){
                $kq_total += $squad_month_temp[$v['id']]['kq_rate'];
                if($squad_month_temp[$v['id']]['kq_rate'] > 0){
                    $kq_count ++;
                }

                $zy_total += $squad_month_temp[$v['id']]['zy_rate'];
                if($squad_month_temp[$v['id']]['zy_rate'] > 0){
                    $zy_count ++;
                }

                $jx_total += $squad_month_temp[$v['id']]['jx_rate'];
                if($squad_month_temp[$v['id']]['jx_rate'] > 0){
                    $jx_count ++;
                }

                $lost_total += $squad_month_temp[$v['id']]['wastage_rate'];
                $lost_num += $squad_month_temp[$v['id']]['wastage_num'];
            }
        }
        $lost_count = count($group);
        if($lost_count == 0){
            $lost_count = 1;
        }
        if($kq_count == 0){
            $kq_count = 1;
        }
        if($jx_count == 0){
            $jx_count = 1;
        }
        if($zy_count == 0){
            $zy_count = 1;
        }

        $temp = array();
        $temp = array('分组',Class_section_group::find($squad_table->section)->name,'-',Class_section_group::find($squad_table->group)->name,'-',round($kq_total*100/$kq_count,2).'%',round($jx_total*100/$jx_count,2).'%',round($zy_total*100/$zy_count,2).'%',round($lost_total*100/$lost_count,2).'%',$lost_num);
        $data[] = $temp;

        //获取班型情况
        $group = Squad::where('type',$squad_table->type)->get()->toArray();
        $kq_total=$zy_total=$jx_total=$lost_total=$lost_num=0;
        $kq_count = $zy_count = $jx_count = $lost_count = 0;
        foreach($group as $v){
            //计算平均时要排除0
            if(isset($squad_month_temp[$v['id']]) && !empty($squad_month_temp[$v['id']])){
                $kq_total += $squad_month_temp[$v['id']]['kq_rate'];
                if($squad_month_temp[$v['id']]['kq_rate'] > 0){
                    $kq_count ++;
                }

                $zy_total += $squad_month_temp[$v['id']]['zy_rate'];
                if($squad_month_temp[$v['id']]['zy_rate'] > 0){
                    $zy_count ++;
                }

                $jx_total += $squad_month_temp[$v['id']]['jx_rate'];
                if($squad_month_temp[$v['id']]['jx_rate'] > 0){
                    $jx_count ++;
                }

                $lost_total += $squad_month_temp[$v['id']]['wastage_rate'];
                $lost_num += $squad_month_temp[$v['id']]['wastage_num'];
            }
        }
        $lost_count = count($group);
        if($lost_count == 0){
            $lost_count = 1;
        }
        if($kq_count == 0){
            $kq_count = 1;
        }
        if($jx_count == 0){
            $jx_count = 1;
        }
        if($zy_count == 0){
            $zy_count = 1;
        }

        $temp = array();
        $temp = array('班型',Class_section_group::find($squad_table->section)->name,'-','-',Class_type::find($squad_table->type)->name,round($kq_total*100/$kq_count,2).'%',round($jx_total*100/$jx_count,2).'%',round($zy_total*100/$zy_count,2).'%',round($lost_total*100/$lost_count,2).'%',$lost_num);
        $data[] = $temp;

        //获取开班批次情况
        $time_s = $time_e = 0;
        $time_s = date('Y-m-01 00:00:00',strtotime($squad_table->start_date));
        $time_e = strtotime($time_s.'+1 month');
        $time_s = strtotime($time_s);
        $group = DB::table('squads')->whereRaw('UNIX_TIMESTAMP(`start_date`)>='.$time_s)->whereRaw('UNIX_TIMESTAMP(`start_date`)<'.$time_e)->get()->toArray();
        $kq_total=$zy_total=$jx_total=$lost_total=$lost_num=0;
        $kq_count = $zy_count = $jx_count = $lost_count = 0;
        foreach($group as $v){
            //计算平均时要排除0
            if(isset($squad_month_temp[$v->id]) && !empty($squad_month_temp[$v->id])){
                $kq_total += $squad_month_temp[$v->id]['kq_rate'];
                if($squad_month_temp[$v->id]['kq_rate'] > 0){
                    $kq_count ++;
                }

                $zy_total += $squad_month_temp[$v->id]['zy_rate'];
                if($squad_month_temp[$v->id]['zy_rate'] > 0){
                    $zy_count ++;
                }

                $jx_total += $squad_month_temp[$v->id]['jx_rate'];
                if($squad_month_temp[$v->id]['jx_rate'] > 0){
                    $jx_count ++;
                }

                $lost_total += $squad_month_temp[$v->id]['wastage_rate'];
                $lost_num += $squad_month_temp[$v->id]['wastage_num'];
            }
        }
        $lost_count = count($group);
        if($lost_count == 0){
            $lost_count = 1;
        }
        if($kq_count == 0){
            $kq_count = 1;
        }
        if($jx_count == 0){
            $jx_count = 1;
        }
        if($zy_count == 0){
            $zy_count = 1;
        }

        $temp = array();
        $temp = array('开班批次',Class_section_group::find($squad_table->section)->name,date('Y',strtotime($squad_table->batch)).'年'.date('m',strtotime($squad_table->batch)).'月','-','-',round($kq_total*100/$kq_count,2).'%',round($jx_total*100/$jx_count,2).'%',round($zy_total*100/$zy_count,2).'%',round($lost_total*100/$lost_count,2).'%',$lost_num);
        $data[] = $temp;

        return $data;
    }

    public function custom()
    {
        return Admin::content(function (Content $content) {
            $content->header('班级月度自定义');
            $content->description(trans('admin.list'));

            $content->body($this->custom_search_form());
            //$content->body($this->view_grid(1)->render());
            $content->body($this->custom_view());
        });
    }

    public function custom_search_form()
    {
        return Admin::form(Squad::class,function (Form $form) {
            $form->setTitle('查看');
            $form->row(function ($row) use ($form)
            {
                $row->width(2)->text('year','年')->attribute(['id'=>'year'])->default(date('Y'));
                $row->width(2)->select('term', '学期')->options(function(){
                    return array('0'=>'全部','1'=>'春季学期','2'=>'秋季学期');
                })->attribute(['id'=>'term']);
                $row->width(4)->select('start_time', '开始时间')->options(function(){
                    $arr = array();
                    for($i=1;$i<=22;$i++){
                        $arr[$i] = '第'.$i.'周';
                    }
                    return $arr;
                })->attribute(['id'=>'start_time'])->default('1');
                $row->width(4)->select('end_time', '结束时间')->options(function(){
                    $arr = array();
                    for($i=1;$i<=22;$i++){
                        $arr[$i] = '第'.$i.'周';
                    }
                    return $arr;
                })->attribute(['id'=>'end_time'])->default('1');
            },  $form);


            $form->row(function ($row) use ($form)
            {
                $user_id = Admin::user()->id;
                $row->width(4)->select('section', trans('admin.section'))->options(function(){
                    return Class_section_group::regulate_options();
                })->attribute(['id'=>'section'])->Myload('group', '/api/get_group_user',$user_id);
                $row->width(4)->select('group',trans('admin.group'))->options()->attribute(['id'=>'group']);
                $row->width(4)->select('type',trans('admin.type'))->options(function(){
                    return Class_type::options(2);
                })->attribute(['id'=>'type']);
            },  $form);

            $form->row(function ($row) use ($form)
            {
                $row->width(4)->date('batch','开班批次')->format('YYYY-MM')->attribute(['id'=>'batch']);
                //$row->width(4)->text('start_time','开始时间(包含)')->attribute(['id'=>'startTime']);
                //$row->width(4)->text('end_time','结束时间(包含)')->attribute(['id'=>'endTime']);
                $row->width(4)->select('tutor', '班主任')->options(function(){
                    $role = DB::table('admin_roles')->where('name','班主任')->get(['id']);
                    $id = '';
                    foreach($role as $v){
                        $id = $v->id;
                    }

                    $tutor = DB::table('admin_users as au')->join('admin_role_users as aru','au.id','=','aru.user_id')
                        ->where('aru.role_id',$id)->select('au.en_name','au.id')->get();

                    $arr = array();
                    foreach($tutor as $v){
                        $arr[$v->id] = $v->en_name;
                    }
                    array_unshift($arr,'全部');
                    return $arr;
                })->attribute(['id'=>'tutor']);
                $row->width(3)->html('<div></div><button style="margin-left:65px;" type="button" id="btn_submit" class="btn btn-primary">查询</button>');
            },  $form);

/*            $form->row(function ($row) use ($form)
            {
                $row->width(5)->html('<div></div>');
                $row->width(3)->html('<div></div><button style="margin-left:65px;" type="button" id="btn_submit" class="btn btn-primary">查询</button>');
            },  $form);*/

            $form->disableSubmit();
            $form->disableReset();
            $form->tools(function (Form\Tools $tools) {
                // 去掉`列表`按钮
                $tools->disableList();
                // 去掉`删除`按钮
                $tools->disableDelete();
                // 去掉`查看`按钮
                $tools->disableView();
            });
        });
    }

    public function custom_view()
    {
        return view('admin.squad_month_custom');
    }

    public function custom_submit(Request $request)
    {
        $query = DB::table('squads as s')
            ->join('class_section_groups as cs1','cs1.id','=','s.section')
            ->join('class_section_groups as cs2','cs2.id','=','s.group')
            ->join('admin_users as au','au.id','=','s.class_teacher')
            ->join('class_types as ct','ct.id','=','s.type');
        if(isset($request['type']) && $request['type'] > 0){
            $query = $query->where('s.type',$request['type']);
        }
        if(isset($request['section']) && $request['section'] > 0){
            $query = $query->where('s.section',$request['section']);
        }
        if(isset($request['group']) && $request['group'] > 0){
            $query = $query->where('s.group',$request['group']);
        }
        if(isset($request['tutor']) && $request['tutor'] > 0){
            $query = $query->where('s.class_teacher',$request['tutor']);
        }
        if(isset($request['batch']) && $request['batch'] != ''){
            $query = $query->where('s.batch',$request['batch']);
        }
        $squads = $query->select('s.id','s.batch','s.no',DB::raw('s.name as squad'),DB::raw('cs1.name as section'),DB::raw('cs2.name as group_name'),DB::raw('au.en_name as tutor'),DB::raw('ct.name as type_name'))->get();
        $squads_id = array();

        foreach($squads as $v){
            $squads_id[$v->id] = $v;
        }

        $query = DB::table('squad_month');
        if(count($squads_id) > 0){
            $query = $query->whereIn('squad_id',array_keys($squads_id));
        }
        if(isset($request['year']) && $request['year'] != ''){
            $query = $query->where('year',(int)$request['year']);
        }
        if(isset($request['term']) && $request['term'] > 0){
            $query = $query->where('season',(int)$request['term']);
        }
        if(isset($request['start_time']) && $request['start_time'] != '' && isset($request['end_time']) && $request['end_time'] != ''){
            $query = $query->where('week','>=',(int)$request['start_time'])->where('week','<=',(int)$request['end_time']);
        }

        $squad_month = $query->get();

        $data = array();$temp = array();
        $total_kq_count = $total_kq_num = 0;
        $total_zy_count = $total_zy_num = 0;
        $total_jx_count = $total_jx_num = 0;
        $total_lost_count = $total_lost_num = 0;
        $total_test_count = $total_test_num = 0;
        $total_test_count_avg = $total_test_num_avg = 0;
        $total_reading_count = $total_reading_num = 0;
        foreach($squad_month as $v){
            if(!isset($temp[$v->squad_id])){
                $temp[$v->squad_id] = array();
                $temp[$v->squad_id]['kq_count'] = 0;
                $temp[$v->squad_id]['kq_num'] = 0;
                $temp[$v->squad_id]['zy_count'] = 0;
                $temp[$v->squad_id]['zy_num'] = 0;
                $temp[$v->squad_id]['jx_count'] = 0;
                $temp[$v->squad_id]['jx_num'] = 0;
                $temp[$v->squad_id]['lost_count'] = 0;
                $temp[$v->squad_id]['week'] = 9999;
                $temp[$v->squad_id]['stu_num'] = 0;
                $temp[$v->squad_id]['jx_avg_count'] = 0;
                $temp[$v->squad_id]['jx_avg_num'] = 0;
                $temp[$v->squad_id]['test_rate_count'] = 0;
                $temp[$v->squad_id]['test_rate_num'] = 0;
                $temp[$v->squad_id]['test_avg_count'] = 0;
                $temp[$v->squad_id]['test_avg_num'] = 0;
                $temp[$v->squad_id]['reading_count'] = 0;
                $temp[$v->squad_id]['teaching_desc'] = '';
                $temp[$v->squad_id]['teaching_chapter'] = '';
                $temp[$v->squad_id]['reading_chapter'] = '';
                $temp[$v->squad_id]['reading_title'] = '';
                $temp[$v->squad_id]['A_Z_title'] = '';
                $temp[$v->squad_id]['A_Z_num'] = 0;
            }
            if($v->week < $temp[$v->squad_id]['week']){
                $temp[$v->squad_id]['week'] = $v->week;
                $temp[$v->squad_id]['stu_num'] = $v->stu_num;
            }
            if($v->kq_rate > 0){
                $temp[$v->squad_id]['kq_count'] += $v->kq_rate;
                $temp[$v->squad_id]['kq_num'] += 1;
                $total_kq_count += $v->kq_rate;
                $total_kq_num += 1;
            }
            if($v->zy_rate > 0){
                $temp[$v->squad_id]['zy_count'] += $v->zy_rate;
                $temp[$v->squad_id]['zy_num'] += 1;
                $total_zy_count += $v->zy_rate;
                $total_zy_num += 1;
            }
            if($v->jx_rate > 0){
                $temp[$v->squad_id]['jx_count'] += $v->jx_rate;
                $temp[$v->squad_id]['jx_num'] += 1;
                $total_jx_count += $v->jx_rate;
                $total_jx_num += 1;
            }

            if($v->jx_avg > 0){
                $temp[$v->squad_id]['jx_avg_count'] += $v->jx_avg;
                $temp[$v->squad_id]['jx_avg_num'] += 1;
            }
            if($v->test_avg > 0){
                $temp[$v->squad_id]['test_avg_count'] += $v->test_avg;
                $temp[$v->squad_id]['test_avg_num'] += 1;
                $total_test_count_avg += $v->test_avg;
                $total_test_num_avg += 1;
            }
            if($v->test_rate > 0){
                $temp[$v->squad_id]['test_rate_count'] += $v->test_rate;
                $temp[$v->squad_id]['test_rate_num'] += 1;
                $total_test_count += $v->test_rate;
                $total_test_num += 1;
            }

            $json = json_decode($v->study_jindu,true);
            if(isset($json['reading'][2]['percent']) && $json['reading'][2]['percent'] > 0){
                $temp[$v->squad_id]['reading_count'] = $json['reading'][2]['percent'];
                $total_reading_count += $json['reading'][2]['percent'];
                $total_reading_num += 1;
            }
            $temp[$v->squad_id]['teaching_desc'] = isset($json['teaching'][0]['desc'])?$json['teaching'][0]['desc']:'';
            $temp[$v->squad_id]['teaching_chapter'] = isset($json['teaching'][0]['chapter'])?$json['teaching'][0]['chapter']:'';
            if(isset($json['reading']) && count($json['reading']) > 0){
                foreach($json['reading'] as $_v){
                    if(isset($_v['title']) && $_v['title'] != 'A to Z'){
                        $temp[$v->squad_id]['reading_chapter'] = $_v['chapters'];
                        $temp[$v->squad_id]['reading_title'] = $_v['title'];
                    }elseif(isset($_v['title']) && $_v['title'] == 'A to Z'){
                        $temp[$v->squad_id]['A_Z_title'] = $_v['atoz'];
                        $temp[$v->squad_id]['A_Z_num'] = is_null($_v['num'])?0:$_v['num'];
                    }
                }
            }

            $temp[$v->squad_id]['lost_count'] += $v->wastage_num;
            $temp[$v->squad_id]['content'] = $v->content;
            $temp[$v->squad_id]['study_jindu'] = json_decode($v->study_jindu,true);
        }

        if($total_kq_num == 0){
            $total_kq_num = 1;
        }
        if($total_zy_num == 0){
            $total_zy_num = 1;
        }
        if($total_jx_num == 0){
            $total_jx_num = 1;
        }

        foreach($temp as $k => $v){
            $data[$k]['squad_id'] = $k;
            $data[$k]['content'] = $v['content'];

            if($v['kq_num'] == 0){
                $data[$k]['kq_rate'] = $v['kq_count']/1;
            }else{
                $data[$k]['kq_rate'] = $v['kq_count']/$v['kq_num'];
            }

            if($v['zy_num'] == 0){
                $data[$k]['zy_rate'] = $v['zy_count']/1;
            }else{
                $data[$k]['zy_rate'] = $v['zy_count']/$v['zy_num'];
            }

            if($v['jx_num'] == 0){
                $data[$k]['jx_rate'] = $v['jx_count']/1;
            }else{
                $data[$k]['jx_rate'] = $v['jx_count']/$v['jx_num'];
            }

            if($v['jx_avg_num'] == 0){
                $data[$k]['jx_avg'] = 0;
            }else{
                $data[$k]['jx_avg'] = $v['jx_avg_count']/$v['jx_avg_num'];
            }

            if($v['test_avg_num'] == 0){
                $data[$k]['test_avg'] = 0;
            }else{
                $data[$k]['test_avg'] = $v['test_avg_count']/$v['test_avg_num'];
            }
            if($v['test_rate_num'] == 0){
                $data[$k]['test_rate'] = 0;
            }else{
                $data[$k]['test_rate'] = $v['test_rate_count']/$v['test_rate_num'];
            }

            $data[$k]['reading_percent'] = $v['reading_count'];

            $total_lost_count += $v['lost_count'];
            $total_lost_num += $v['stu_num'];
            $data[$k]['wastage_num'] = $v['lost_count'];
            if($v['stu_num'] == 0){
                $data[$k]['wastage_rate'] = $v['lost_count']/1;
            }else{
                $data[$k]['wastage_rate'] = $v['lost_count']/$v['stu_num'];
            }

            $data[$k]['teaching_desc'] = $v['teaching_desc'];
            $data[$k]['teaching_chapter'] = $v['teaching_chapter'];
            $data[$k]['reading_chapter'] = $v['reading_chapter'];
            $data[$k]['reading_title'] = $v['reading_title'];
            $data[$k]['A_Z_title'] = $v['A_Z_title'];
            $data[$k]['A_Z_num'] = $v['A_Z_num'];


            $data[$k]['squad'] = $squads_id[$k]->squad;
            $data[$k]['group_name'] = $squads_id[$k]->group_name;
            $data[$k]['section'] = $squads_id[$k]->section;
            $data[$k]['type_name'] = $squads_id[$k]->type_name;
            $data[$k]['no'] = $squads_id[$k]->no;
            $data[$k]['tutor'] = $squads_id[$k]->tutor;
            $data[$k]['batch'] = $squads_id[$k]->batch;
        }

        $return = array_chunk($data,$request['pagesize']);
        $data = $return[$request['page']-1];
/*        $query1 = clone $query;
        $total_data = $query1->select(DB::raw('AVG(kq_rate) as kq'),DB::raw('AVG(zy_rate) as zy'),DB::raw('AVG(jx_rate) as jx'),DB::raw('AVG(wastage_rate) as wastage'),DB::raw('sum(wastage_num) as num'))->get();
        $data = $query->offset(($request['page']-1)*$request['pagesize'])->limit($request['pagesize'])->get();

        foreach($data as $k=>$value){
            $data[$k] = array_merge((array)$value,(array)$squads_id[$value->squad_id]);
        }*/
        $total_kq_num = $total_kq_num ? $total_kq_num : 1;
        $total_jx_num = $total_jx_num ? $total_jx_num : 1;
        $total_zy_num = $total_zy_num ? $total_zy_num : 1;
        $total_test_num = $total_test_num ? $total_test_num : 1;
        $total_test_num_avg = $total_test_num_avg ? $total_test_num_avg : 1;
        $total_lost_num = $total_lost_num ? $total_lost_num : 1;

        $total_data[0]['kq'] = round($total_kq_count /  $total_kq_num,2);
        $total_data[0]['jx'] = round($total_jx_count /  $total_jx_num,2);
        $total_data[0]['zy'] = round($total_zy_count /  $total_zy_num,2);
        $total_data[0]['test_rate'] = round($total_test_count /  $total_test_num,2);
        $total_data[0]['test_avg'] = round($total_test_count_avg /  $total_test_num_avg,2);
        if($total_reading_num == 0){
            $total_data[0]['reading_percent'] = 0;
        }else{
            $total_data[0]['reading_percent'] = round($total_reading_count /  $total_reading_num,2);
        }
        $total_data[0]['wastage'] = round($total_lost_count /  $total_lost_num,2);
        $total_data[0]['num'] = $total_lost_count;

        return array('data'=>$data,'total_data'=>$total_data);
    }


    public function custom_export(Request $request)
    {
        ini_set('memory_limit','500M');
        set_time_limit(0);//设置超时限制为0分钟
        Excel::create('班级周报自定义' . date('YmdHis'), function ($excel) use ($request) {
            $excel->sheet('班级周报自定义', function ($sheet) use ($request) {
                $title=array('班段','开班批次','分组','班型','班号','班主任','全勤率','家校准交率','家校平均分','作业准交率',
                    '测试参与度','测试平均分','阅读计划完成率','完成绘本名称','A-Z读本','篇目数','所学章节','知识点','流失率','流失人数');
                $sheet->rows([$title]);

                $datas = $this->get_export_data($request);
                $sheet->rows($datas);
            });

        })->export('xls');
        ob_end_clean();
    }

    public function get_export_data($request)
    {
        $query = DB::table('squads as s')
            ->join('class_section_groups as cs1','cs1.id','=','s.section')
            ->join('class_section_groups as cs2','cs2.id','=','s.group')
            ->join('admin_users as au','au.id','=','s.class_teacher')
            ->join('class_types as ct','ct.id','=','s.type');
        if(isset($request['type']) && $request['type'] > 0){
            $query = $query->where('s.type',$request['type']);
        }
        if(isset($request['section']) && $request['section'] > 0){
            $query = $query->where('s.section',$request['section']);
        }
        if(isset($request['group']) && $request['group'] > 0){
            $query = $query->where('s.group',$request['group']);
        }
        if(isset($request['tutor']) && $request['tutor'] > 0){
            $query = $query->where('s.class_teacher',$request['tutor']);
        }
        if(isset($request['batch']) && $request['batch'] != ''){
            $query = $query->where('s.batch',$request['batch']);
        }
        $squads = $query->select('s.id','s.batch','s.no',DB::raw('s.name as squad'),DB::raw('cs1.name as section'),DB::raw('cs2.name as group_name'),DB::raw('au.en_name as tutor'),DB::raw('ct.name as type_name'))->get();
        $squads_id = array();

        foreach($squads as $v){
            $squads_id[$v->id] = $v;
        }

        $query = DB::table('squad_month');
        if(count($squads_id) > 0){
            $query = $query->whereIn('squad_id',array_keys($squads_id));
        }
        if(isset($request['year']) && $request['year'] != ''){
            $query = $query->where('year',(int)$request['year']);
        }
        if(isset($request['term']) && $request['term'] > 0){
            $query = $query->where('season',(int)$request['term']);
        }
        if(isset($request['start_time']) && $request['start_time'] != '' && isset($request['end_time']) && $request['end_time'] != ''){
            $query = $query->where('week','>=',(int)$request['start_time'])->where('week','<=',(int)$request['end_time']);
        }

        $squad_month = $query->get();

        $data = array();$temp = array();

        foreach($squad_month as $v){
            if(!isset($temp[$v->squad_id])){
                $temp[$v->squad_id] = array();
                $temp[$v->squad_id]['kq_count'] = 0;
                $temp[$v->squad_id]['kq_num'] = 0;
                $temp[$v->squad_id]['zy_count'] = 0;
                $temp[$v->squad_id]['zy_num'] = 0;
                $temp[$v->squad_id]['jx_count'] = 0;
                $temp[$v->squad_id]['jx_num'] = 0;
                $temp[$v->squad_id]['lost_count'] = 0;
                $temp[$v->squad_id]['week'] = 9999;
                $temp[$v->squad_id]['stu_num'] = 0;
                $temp[$v->squad_id]['jx_avg_count'] = 0;
                $temp[$v->squad_id]['jx_avg_num'] = 0;
                $temp[$v->squad_id]['test_rate_count'] = 0;
                $temp[$v->squad_id]['test_rate_num'] = 0;
                $temp[$v->squad_id]['test_avg_count'] = 0;
                $temp[$v->squad_id]['test_avg_num'] = 0;
                $temp[$v->squad_id]['reading_count'] = 0;
                $temp[$v->squad_id]['teaching_desc'] = '';
                $temp[$v->squad_id]['teaching_chapter'] = '';
                $temp[$v->squad_id]['reading_chapter'] = '';
                $temp[$v->squad_id]['reading_title'] = '';
                $temp[$v->squad_id]['A_Z_title'] = '';
                $temp[$v->squad_id]['A_Z_num'] = 0;
            }
            if($v->week < $temp[$v->squad_id]['week']){
                $temp[$v->squad_id]['week'] = $v->week;
                $temp[$v->squad_id]['stu_num'] = $v->stu_num;
            }
            if($v->kq_rate > 0){
                $temp[$v->squad_id]['kq_count'] += $v->kq_rate;
                $temp[$v->squad_id]['kq_num'] += 1;
            }
            if($v->zy_rate > 0){
                $temp[$v->squad_id]['zy_count'] += $v->zy_rate;
                $temp[$v->squad_id]['zy_num'] += 1;
            }
            if($v->jx_rate > 0){
                $temp[$v->squad_id]['jx_count'] += $v->jx_rate;
                $temp[$v->squad_id]['jx_num'] += 1;
            }

            if($v->jx_avg > 0){
                $temp[$v->squad_id]['jx_avg_count'] += $v->jx_avg;
                $temp[$v->squad_id]['jx_avg_num'] += 1;
            }

            if($v->test_avg > 0){
                $temp[$v->squad_id]['test_avg_count'] += $v->test_avg;
                $temp[$v->squad_id]['test_avg_num'] += 1;
            }
            if($v->test_rate > 0){
                $temp[$v->squad_id]['test_rate_count'] += $v->test_rate;
                $temp[$v->squad_id]['test_rate_num'] += 1;
            }

            $json = json_decode($v->study_jindu,true);
            if(isset($json['reading'][2]['percent']) && $json['reading'][2]['percent'] > 0){
                $temp[$v->squad_id]['reading_count'] = $json['reading'][2]['percent'];
            }
            $temp[$v->squad_id]['teaching_desc'] = isset($json['teaching'][0]['desc'])?$json['teaching'][0]['desc']:'';
            $temp[$v->squad_id]['teaching_chapter'] = isset($json['teaching'][0]['chapter'])?$json['teaching'][0]['chapter']:'';
            if(isset($json['reading']) && count($json['reading']) > 0){
                foreach($json['reading'] as $_v){
                    if(isset($_v['title']) && $_v['title'] != 'A to Z'){
                        $temp[$v->squad_id]['reading_chapter'] = $_v['chapters'];
                        $temp[$v->squad_id]['reading_title'] = $_v['title'];
                    }elseif(isset($_v['title']) && $_v['title'] == 'A to Z'){
                        $temp[$v->squad_id]['A_Z_title'] = $_v['atoz'];
                        $temp[$v->squad_id]['A_Z_num'] = is_null($_v['num'])?0:$_v['num'];
                    }
                }
            }


            $temp[$v->squad_id]['lost_count'] += $v->wastage_num;
            $temp[$v->squad_id]['content'] = $v->content;
        }

        foreach($temp as $k => $v){
            $data[$k][] = $squads_id[$k]->section;
            $data[$k][] = $squads_id[$k]->batch;
            $data[$k][] = $squads_id[$k]->group_name;
            $data[$k][] = $squads_id[$k]->type_name;
            $data[$k][] = $squads_id[$k]->no;
            $data[$k][] = $squads_id[$k]->tutor;

            if($v['kq_num'] == 0){
                $data[$k][] = $v['kq_count']/1;
            }else{
                $data[$k][] = $v['kq_count']/$v['kq_num'];
            }

            if($v['jx_num'] == 0){
                $data[$k][] = $v['jx_count']/1;
            }else{
                $data[$k][] = $v['jx_count']/$v['jx_num'];
            }

            if($v['jx_avg_num'] == 0){
                $data[$k][] = 0;
            }else{
                $data[$k][] = $v['jx_avg_count']/$v['jx_avg_num'];
            }

            if($v['zy_num'] == 0){
                $data[$k][] = $v['zy_count']/1;
            }else{
                $data[$k][] = $v['zy_count']/$v['zy_num'];
            }

            if($v['test_rate_num'] == 0){
                $data[$k][] = 0;
            }else{
                $data[$k][] = $v['test_rate_count']/$v['test_rate_num'];
            }

            if($v['test_avg_num'] == 0){
                $data[$k][] = 0;
            }else{
                $data[$k][] = $v['test_avg_count']/$v['test_avg_num'];
            }

            $data[$k][] = $v['reading_count']/100;

            $data[$k][] = $v['reading_chapter'];
            $data[$k][] = $v['A_Z_title'];
            $data[$k][] = $v['A_Z_num'];
            $data[$k][] = $v['teaching_chapter'];
            $data[$k][] = $v['teaching_desc'];


            if($v['stu_num'] == 0){
                $data[$k][] = 0;
            }else{
                $data[$k][] = $v['lost_count']/$v['stu_num'];
            }
            $data[$k][] = $v['lost_count'];
        }

        return $data;
    }
}
